package gs.gui;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class ConnectionController {
	
	Connection connection;


	public boolean connect(String ip, String port, String user, String password)
	{
		Connection con = null;

        String cs = "jdbc:mysql://"+ip+":"+port+"/game_db";

        try {

            con = DriverManager.getConnection(cs, user, password);

        } catch (SQLException ex) {
        	System.out.println(ex.getMessage());
            return false;

        } finally {

                if (con != null) {
                    connection = con;
                }

        }
        
        return true;
	}
	
	public String createAccount(String login, String password)
	{
		try {
			Statement s = connection.createStatement();
			s.executeQuery("CALL create_account('"+login+"', '"+password+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String createChar(String charName, String accName)
	{
		try {
			Statement s = connection.createStatement();
			s.executeQuery("CALL create_character('"+charName+"', '"+accName+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String createItem(String itemName, String level, String type) {
		try {
			Statement s = connection.createStatement();
			s.executeQuery("CALL create_item('"+itemName+"', '"+type+"', "+level+")");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String createSpell(String spellName, String type, String damage,
			String cost, String range) {
		try {
			Statement s = connection.createStatement();
			s.executeQuery("CALL create_spell('"+spellName+"', '"+type+"', "+damage+", "+cost+" , "+range+")");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}

	public String createNPC(String npcName, String health, String level,
			String spell1, String spell2, String spell3) {
		try {
			Statement s = connection.createStatement();
			s.executeQuery("CALL create_npc('"+npcName+"', "+health+", "+level+", '"+spell1+"' , '"+spell2+"', '"+spell3+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String createQuest(String questName, String type, String reward,
			String itemCount, String item, String npcCount, String npc) {
		try {
			Statement s = connection.createStatement();
			System.out.println("CALL create_quest('"+questName+"', '"+type+"', '"+reward+"', "+itemCount+" , '"+item+"', "+npcCount+", '"+npc+"')");
			
			s.executeQuery("CALL create_quest('"+questName+"', '"+type+"', '"+reward+"', "+itemCount+", '"+item+"', "+npcCount+", '"+npc+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String deleteAccount(String login)
	{
		try {
			Statement s = connection.createStatement();
			s = connection.createStatement();
			s.executeQuery("CALL delete_account('"+login+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}	
	
	public String deleteChar(String charName)
	{
		try {
			Statement s = connection.createStatement();
			s = connection.createStatement();
			s.executeQuery("CALL delete_character('"+charName+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String deleteItem(String itemName) {
		try {
			Statement s = connection.createStatement();
			s = connection.createStatement();
			s.executeQuery("CALL delete_item('"+itemName+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}

	public String deleteSpell(String spellName) {
		try {
			Statement s = connection.createStatement();
			s = connection.createStatement();
			s.executeQuery("CALL delete_spell('"+spellName+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String deleteNPC(String npcName) {
		try {
			Statement s = connection.createStatement();
			s = connection.createStatement();
			s.executeQuery("CALL delete_npc('"+npcName+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	public String deleteQuest(String questName) {
		try {
			Statement s = connection.createStatement();
			s = connection.createStatement();
			s.executeQuery("CALL delete_quest('"+questName+"')");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}
	
	
	public ArrayList<String> getAccountList()
	{
		ResultSet rs = null;
		ArrayList<String> a = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_accounts()");
			
			while(rs.next())
			{
				a.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return a;
	}
	
	public ArrayList<String> getCharList(String accName)
	{
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_characters2('"+accName+"')");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}
	
	public ArrayList<String> getItemList() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_items()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public ArrayList<String> getSpellList() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_spells()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}
	
	public ArrayList<String> getNPCList() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_npc()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}
	
	public ArrayList<String> getQuestList() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_quests()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public ArrayList<String> getSpellTypes() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_spell_types()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}
	
	public ArrayList<String> getItemTypes() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_item_types()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}


	public ArrayList<String> getQuestTypes() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_quest_types()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public ArrayList<String> getItemData(String name) {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_item_data('"+name+"')");
			
			if(rs.next())
			{
				c.add(rs.getString(2));
				c.add(rs.getString(3));
				c.add(rs.getString(4));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public ArrayList<String> getSpellData(String name) {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_spell_data('"+name+"')");
			
			if(rs.next())
			{
				c.add(rs.getString(2));
				c.add(rs.getString(3));
				c.add(rs.getString(4));
				c.add(rs.getString(5));
				c.add(rs.getString(6));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public ArrayList<String> getNPCData(String name) {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_npc_data('"+name+"')");
			
			if(rs.next())
			{
				c.add(rs.getString(2));
				c.add(rs.getString(3));
				c.add(rs.getString(4));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public ArrayList<String> getQuestData(String name) {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_quest_data('"+name+"')");
			
			if(rs.next())
			{
				c.add(rs.getString(2));
				c.add(rs.getString(3));
				c.add(rs.getString(4));
				c.add(rs.getString(5));
				c.add(rs.getString(6));
				c.add(rs.getString(7));
				c.add(rs.getString(8));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public String createNPCLocation(String npcName, String locationName,
			String x, String y) {
		try {
			Statement s = connection.createStatement();
			s.executeQuery("CALL add_npc_to_location('"+npcName+"', '"+locationName+"', "+x+", "+y+")");
		} catch (SQLException e)
		{
			e.printStackTrace();
			return e.getMessage();
		}
		return null;
	}

	public ArrayList<String> getNPCLocationList() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_npc_locations()");
			
			while(rs.next())
			{
				c.add(rs.getString(2)+" - "+rs.getString(3)+", x:"+rs.getString(4)+", y:"+rs.getString(5));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}

	public ArrayList<String> getLocationList() {
		ResultSet rs = null;
		ArrayList<String> c = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			rs = statement.executeQuery("CALL get_existing_locations()");
			
			while(rs.next())
			{
				c.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}



	
}
